﻿if db_id('SQLtest') is not null
  begin
	drop database SQLtest
  end
  create database SQLtest
  on
  (
	NAME='SQLtest_data',
	FILENAME='D:\Database\SQLtest_data.mdf',
	SIZE=5MB,
	MAXSIZE=100MB, 
	FILEGROWTH=10%
  )
  LOG ON
 (
    NAME='SQLtest_log',
    FILENAME='D:\Database\SQLtest_log.ldf',
    SIZE=1MB,
    FILEGROWTH=1MB 
  )
GO


USE SQLtest
if object_id('Card') is not null
	begin
		drop table Card
	end
CREATE  TABLE  Card
(
	CNo int identity(1,1) not null,--借书卡号
	Name varchar(20) not null,--姓名
	ClassName varchar(100) not null,--班级名称
) 
GO

USE SQLtest
if object_id('Books') is not null
	begin
		drop table Books
	end
CREATE  TABLE  Books
(
	BNo int identity(1,1) not null,--书号
	BName varchar(50) not null,--书名
	Author varchar(20) not null,--作者
	Price money not null,--单价
	Quantity int not null,--库存册数
) 
GO

USE SQLtest
if object_id('Borrow') is not null
	begin
		drop table Borrow
	end
CREATE  TABLE  Borrow
(
	CNo int not null,--借书卡号
	BNo int not null,--书号
	RDate Date not null,--还书日期
	BDate Date not null,--借书日期
) 
GO

--添加主键
  alter table [Card]
  add constraint PK_Card_CNo primary key(CNo)
  alter table Books
  add constraint PK_Books_BNo primary key(BNo)
  alter table Borrow
  add constraint PK_Borrow_CNo_BNo primary key(CNo,BNo)

--添加主外键约束
alter table Borrow
add 
constraint FK_Card_Borrow_CNo foreign key(CNo) references [Card](CNo),
constraint FK_Books_Borrow_BNo foreign key(BNo) references Books(BNo)

--添加[Card]数据
insert into [Card](Name,ClassName)
values('王二','17班')
insert into [Card](Name,ClassName)
values('张三','7班')
insert into [Card](Name,ClassName)
values('李四','8班')
insert into [Card](Name,ClassName)
values('小明','9班')
insert into [Card](Name,ClassName)
values('小红','2班')
insert into [Card](Name,ClassName)
values('小黑','1班')
insert into [Card](Name,ClassName)
values('小白','18班')
insert into [Card](Name,ClassName)
values('小王','18班')
insert into [Card](Name,ClassName)
values('小张','3班')
insert into [Card](Name,ClassName)
values('小七','4班')
insert into [Card](Name,ClassName)
values('小吴','C01')
insert into [Card](Name,ClassName)
values('小噶','C01')

--添加Books数据
insert into Books(BName,Author,Price,Quantity)
values('朝花夕拾','鲁迅',40,6)
insert into Books(BName,Author,Price,Quantity)
values('荷塘月色','朱自清',32,3)
insert into Books(BName,Author,Price,Quantity)
values('水浒传','施耐庵',30,5)
insert into Books(BName,Author,Price,Quantity)
values('三国演义','罗贯中',40,6)
insert into Books(BName,Author,Price,Quantity)
values('西游记','吴承恩',28,7)
insert into Books(BName,Author,Price,Quantity)
values('红楼梦','曹雪芹',20,9)
insert into Books(BName,Author,Price,Quantity)
values('Java数据结构和算法','Java',60,6)
insert into Books(BName,Author,Price,Quantity)
values('SQL Server 从入门到精通','SQL',70,8)
insert into Books(BName,Author,Price,Quantity)
values('Spring Boot实战','Spring',80,5)
insert into Books(BName,Author,Price,Quantity)
values('SSM轻量级框架应用实战','SSM',90,2)
insert into Books(BName,Author,Price,Quantity)
values('网络科技大全','网友制作',888,1)
----添加Borrow数据
insert into Borrow(CNo,BNo,RDate,BDate)
values(1,2,'2018-6-5','2018-4-6')
insert into Borrow(CNo,BNo,RDate,BDate)
values(2,6,'2018-4-3','2018-3-6')
insert into Borrow(CNo,BNo,RDate,BDate)
values(3,1,'2017-7-5','2017-5-6')
insert into Borrow(CNo,BNo,RDate,BDate)
values(4,3,'2018-4-2','2018-2-16')
insert into Borrow(CNo,BNo,RDate,BDate)
values(5,5,'2017-4-5','2017-1-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(6,10,'2018-12-5','2017-2-6')
insert into Borrow(CNo,BNo,RDate,BDate)
values(7,9,'2018-5-15','2018-2-26')
insert into Borrow(CNo,BNo,RDate,BDate)
values(8,7,'2018-4-6','2018-3-6')
insert into Borrow(CNo,BNo,RDate,BDate)
values(9,4,'2018-4-15','2018-2-2')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,8,'2017-9-21','2017-6-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,1,'2017-2-21','2016-6-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,2,'2018-7-12','2017-6-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,3,'2017-8-21','2017-6-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,4,'2018-9-11','2018-6-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(10,6,'2018-5-21','2018-3-12')
insert into Borrow(CNo,BNo,RDate,BDate)
values(9,6,'2016-4-21','2016-3-12')

--2.查询超过5本的读者
select CNo as '借书卡号',COUNT(BNo) as '图书册数'
from Borrow
GROUP BY CNo having COUNT(BNo)>5

--3.查询借阅了"水浒传"一书的读者
select Name as '姓名',ClassName as '所在班级'
from [Card],Books,Borrow
where BName='水浒传' and Borrow.CNo=[Card].CNo and Borrow.BNo=Books.BNo

-- 4.查询过期未还图书
select BNo as '书号',CNo as '借书卡号',RDate as '还书日期'
from Borrow
where RDate > GETDATE()

--5．查询书名包括"网络"关键词的图书
select BNo as '书号',BName as '书名',Author as '作者'
from Books
where BName like '%网络%'

--6.查询现有图书中价格最高的图书
select BName as '书名',Author as '作者'
from Books
where Price=(select MAX(Price) from Books)
--7.查询当前借了"Java数据结构和算法(第二版•影印版)"但没有借"SQL Server 从入门到精通（第2版）"的读者
select CNo from Borrow w,Books s
where w.BNo=s.BNo and s.BName like '%Java%' and
not exists (select * from Borrow a,Books b where a.BNo=b.BNo and b.BName like '%SQL%')
ORDER BY w.CNO DESC

 --8.将"C01"班同学所借图书的还期都延长一周。
 update b set RDate=DATEADD(Day,7,b.RDate)
from [Card] a,Borrow b
where a.CNo=b.CNo and a.ClassName='C01'
 --9.从Books图书表中删除当前无人借阅的图书记录。
  delete A from Books a
where NOT EXISTS(select * from Borrow where BNo=a.BNo)
 --10.查询当前同时借有"Spring Boot实战"和" SSM轻量级框架应用实战"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
select CNo from Borrow w,Books s
where w.BNo=s.BNo and s.BName like '%Java%' and s.BName like '%SQL%'


